import os
import openpyxl
import re
import numpy as np

class Worktime:
    def __init__(self, dir, dept):
        self.__dir = dir
        self.__dept = dept
        self.__project_month_staff_time = {}
        self.__worktimes = np.empty(14, dtype=object)

    def input(self):
        for folderName, subfolders, filenames in os.walk(self.__dir):
            for filename in filenames:
                split_names = os.path.basename(filename).split('.')
                excel_name = split_names[0]
                ext_name = split_names[1]
                if ext_name == 'xlsx':
                    workbook = openpyxl.load_workbook(self.__dir + "/" + filename)
                    worksheet = workbook[excel_name]
                    month = re.compile(r'\d{2}月').search(filename).group()
                    # 外包
                    if re.compile(r'_\w{2}').search(filename).group() == '_外勤':
                        project_ix = 11
                        staff_ix = 1
                        time_ix = 14
                        dept_ix = 10
                    else:
                        # 内部
                        project_ix = 5
                        staff_ix = 1
                        time_ix = 10
                        dept_ix = 7
                    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                        if self.__dept != row[dept_ix].value:
                            continue
                        project = row[project_ix].value
                        time = row[time_ix].value
                        staff = row[staff_ix].value
                        if project not in self.__project_month_staff_time.keys():
                            self.__project_month_staff_time[project] = {month:{staff:time}}
                        elif month not in self.__project_month_staff_time[project].keys():
                            self.__project_month_staff_time[project][month] = {staff:time}
                        elif staff not in self.__project_month_staff_time[project][month].keys():
                            self.__project_month_staff_time[project][month][staff] = time
                        else:
                            self.__project_month_staff_time[project][month][staff] =round((float(time) + float(self.__project_month_staff_time[project][month][staff])), 2)
        print(self.__project_month_staff_time)

    def process(self):
        head = ['项目名称','姓名','01月','02月','03月','04月','05月','06月','07月','08月','09月','10月','11月','12月']
        col_project_ix = 0
        col_staff_ix = 1
        self.__worktimes = np.array(head)
        for project in self.__project_month_staff_time.keys():
            for month in self.__project_month_staff_time[project].keys():
                for staff in self.__project_month_staff_time[project][month].keys():
                    row = np.empty(14, dtype=object)
                    row[col_project_ix] = project
                    row[col_staff_ix] = staff
                    row[head.index(month)] = self.__project_month_staff_time[project][month][staff]
                    self.__worktimes = np.vstack((self.__worktimes, row))
        print(self.__worktimes)

    def output(self):
        wb = openpyxl.Workbook()
        ws = wb.active
        for i in range(0, self.__worktimes.shape[0]):
            ws.append(self.__worktimes[i].tolist())
        wb.save("工时统计2.xlsx")

wt = Worktime('/Users/zhangxiangbin/wps-sync/02 招商金科/03项目管理/2024年/工时填报', '资管应用研发部')
wt.input()
wt.process()
wt.output()
